package com.fg.cloud.common.file.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import com.alibaba.fastjson.JSONObject;
import com.fg.cloud.common.CommonConstants.FilePath;
import com.fg.cloud.common.IDUtil;
import com.fg.cloud.common.file.PropertiesUtils;
import com.fg.cloud.common.reflection.ParamsReflect;
import com.fg.cloud.common.tuple.TwoTuple;



/**
 * Excel导出核心类，作用于导出数据，注入
 * @author 周围
 *
 */
public class ExcelUtils {
	/** 金额类型单元格格式化 */
	static DataFormat format = null;
	/** 表头行单元格格式化 */
	static CellStyle headStyle = null;
	static CellStyle titleStyle = null;
	/** 表头行字体单元格格式化 */
	static Font headFont = null;
	static Font titleFont = null;
	
	/**  */
	static CellStyle numberStyle = null;
	
	static CellStyle stringStyle = null;
	
	static CellStyle dateStyle = null;
	
	static CellStyle decimalStyle = null;

	
	/**
	 * Description:excel构造初始化加载默认设置
	 * @param work
	 * @author around
	 * @date 2017年8月29日下午12:00:12
	 */
	private static void initWorkBook(Workbook work) {
		format = work.createDataFormat();
		headStyle = work.createCellStyle();
		headFont = work.createFont();
		titleFont = work.createFont();
		
		//set font 2 to 10 point type
		headFont.setFontHeightInPoints((short) 12);
		headFont.setColor((short)Font.COLOR_NORMAL);
		headFont.setFontName("宋体");
		//headFont.setBold(true);
		//headFont.setStrikeout(true);
		titleFont = headFont;
		titleFont.setFontHeightInPoints((short) 18);
		titleFont.setBold(true);
		
		headStyle.setFont(headFont);
		headStyle.setDataFormat(format.getFormat("#,##0.0"));
		headStyle.setFillForegroundColor(HSSFColor.GOLD.index);  
		headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);  
		headStyle.setBorderBottom(BorderStyle.THIN);  
		headStyle.setBorderLeft(BorderStyle.THIN);  
		headStyle.setBorderRight(BorderStyle.THIN);  
		headStyle.setBorderTop(BorderStyle.THIN);  
		headStyle.setAlignment(HorizontalAlignment.CENTER);
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		titleStyle = headStyle;
		titleStyle.setFont(titleFont);
	}
	
	
	
	

	public static void exportExcel(JSONObject json, String excelName, String serverPath, 
			String[] titles, String[] fields, List<Object> list){
		try {
			//获取系统资源
			Properties prop = new PropertiesUtils().getProperties("classpath:resource/resource.properties");
			
			String baseDir = prop.getProperty("excel_temp_dir") + "/";
			
			String fileName = excelName + IDUtil.generalSrid() + ".xlsx";
			File outfile = new File(serverPath + baseDir);
			if(!outfile.exists()) outfile.mkdirs();
			
			outfile = new File(outfile, fileName);
			if(!outfile.exists()) 
				outfile.createNewFile();
			
			json.put("fileName", fileName);
			json.put("filePath", baseDir + fileName);
			
			FileOutputStream fos = new FileOutputStream(outfile);
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			//@SuppressWarnings("unused")
			Sheet sheet = workbook.createSheet(excelName);
			sheet.setDefaultColumnWidth(20); 

			// 生成一个样式  
			CellStyle style = workbook.createCellStyle();  
			
			// 设置这些样式  
			style.setFillForegroundColor(HSSFColor.GOLD.index);  
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
			style.setBorderBottom(BorderStyle.THIN);  
			style.setBorderLeft(BorderStyle.THIN);  
			style.setBorderRight(BorderStyle.THIN);  
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			
			//设置表头自适应
			Row rowheaders = sheet.createRow(0);
			rowheaders.setHeight((short) 500);
			rowheaders.setHeightInPoints(20);
			//设置表头信息
			for (int i = 0; i < titles.length; i++) {
				sheet.autoSizeColumn(i, true);
				Cell cell = rowheaders.createCell(i);  
				cell.setCellStyle(style);  
				HSSFRichTextString text = new HSSFRichTextString(titles[i]);  
				cell.setCellValue(text);
				//cell.setCellType(cellType)
			} 
			//创建数据单元格样式
			CellStyle dataCellStyle = workbook.createCellStyle();
			dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			
			Object obj = null;
			Row row = null;
			Cell cell = null;
			Object temp = null;
			//设置Excel表数据，本导出为统一String格式导出，二期准备对单元格样式进行预处理，对应每一列的结果集自动设置对应数据类型的格式-around
			for (int i = 0; i < list.size(); i++) {
				obj = list.get(i);
				row = sheet.createRow(i+1);
				for (int j = 0; j < fields.length; j++) {
					cell = row.createCell(j);
					cell.setCellStyle(dataCellStyle);
					temp = ParamsReflect.getFieldValue(obj, fields[j]);
					cell.setCellValue(temp == null ? "" : temp.toString());
				}
			}
			
			fos.flush(); 
			workbook.write(fos); 
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	/**
	 * Description:导出Excel，根据已有数据格式
	 * @param serverPath
	 * @param excel
	 * @throws IOException
	 * @author around
	 * @date 2017年8月29日上午10:58:28
	 */
	public static <T extends Object> JSONObject export(String serverPath, ExcelBean excel, 
			List<T> list) throws IOException {
		JSONObject result = new JSONObject();
		//检测并创建对应URL的文件
        String fileName = excel.getExcelName() + IDUtil.generalSrid() + ".xlsx";
        File outfile = new File(serverPath + FilePath.SEPARATOR + FilePath.EXCEL);
		if(!outfile.exists()) outfile.mkdirs();
		
		outfile = new File(outfile, fileName);
		if(!outfile.exists()) 
			outfile.createNewFile();
		//缓存excel物理路径
		result.put("fileName", fileName);
		result.put("filePath", serverPath+FilePath.SEPARATOR+FilePath.EXCEL+
				FilePath.SEPARATOR+fileName);
		
		// keep 100 rows in memory, exceeding rows will be flushed to disk
		SXSSFWorkbook work = new SXSSFWorkbook(100);
		//initWorkBook(work);
        Sheet sheet = work.createSheet();
        sheet.setDefaultColumnWidth(20);
        //创建表头标题
		Row rowheaders = sheet.createRow(0);
		rowheaders.setHeight((short) 1000);
		rowheaders.setHeightInPoints(20);
		//处理表头标题融合
		CellRangeAddress cra =new CellRangeAddress(0, 0, 0, excel.getTitleArray().length-1);
		sheet.addMergedRegion(cra);
		RegionUtil.setBorderBottom(1, cra, sheet); // 下边框
		RegionUtil.setBorderLeft(1, cra, sheet); // 左边框
		RegionUtil.setBorderRight(1, cra, sheet); // 有边框
		RegionUtil.setBorderTop(1, cra, sheet); // 上边框
		//处理表头标题处样式
		Font font = setFont(work.createFont(), (short) 18, Font.COLOR_NORMAL, "黑体", true);
		CellStyle style = setCellStyle(work.createCellStyle(),
				IndexedColors.CORNFLOWER_BLUE.index, HorizontalAlignment.CENTER,
				VerticalAlignment.CENTER);
		style.setFont(font);
		//赋值
		Cell cell = null;
		cell = rowheaders.createCell(0);
		cell.setCellStyle(style);
		cell.setCellValue(excel.getExcelName());
		
		//处理表头列名
		rowheaders = sheet.createRow(1);
		rowheaders.setHeight((short) 1000);
		rowheaders.setHeightInPoints(20);
		//处理表头标题处样式
		font = setFont(work.createFont(), (short) 16, Font.COLOR_NORMAL, "宋体", true);
		style = setCellStyle(work.createCellStyle(),
				IndexedColors.GOLD.index, HorizontalAlignment.CENTER,
				VerticalAlignment.CENTER);
		style.setFont(font);
		for (int i = 0; i < excel.getTitleArray().length; i++) {
			cell = rowheaders.createCell(i);  
			cell.setCellStyle(style);
			cell.setCellType(CellType.STRING);
			cell.setCellValue(excel.getTitleArray()[i]);
		}
		
		//处理数据样式
		font = setFont(work.createFont(), (short) 14, Font.COLOR_NORMAL, "宋体", true);
		stringStyle = setCellStyle(work.createCellStyle(), IndexedColors.AUTOMATIC.index, 
				HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
		stringStyle.setFont(font);
		format.getFormat("#,##0.0");
		format = work.createDataFormat();
		numberStyle = setCellStyle(work.createCellStyle(), IndexedColors.AUTOMATIC.index, 
				HorizontalAlignment.RIGHT, VerticalAlignment.CENTER);
		numberStyle.setFont(font);
		numberStyle.setDataFormat(format.getFormat("#,##0"));
		decimalStyle = setCellStyle(work.createCellStyle(), IndexedColors.AUTOMATIC.index, 
				HorizontalAlignment.RIGHT, VerticalAlignment.CENTER);
		decimalStyle.setFont(font);
		decimalStyle.setDataFormat(format.getFormat("#,##0.0"));
		dateStyle = setCellStyle(work.createCellStyle(), IndexedColors.AUTOMATIC.index, 
				HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
		dateStyle.setFont(font);
		dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
		//处理数据导出
		Row row = null;
		T t = null;
        TwoTuple<String, Object> data = null;
		
        for(int index = 0, count = list.size(); index < count; index++){
            t = list.get(index);
            row = sheet.createRow(index+2);
            for(int cellnum = 0; cellnum < excel.getColumnArray().length; cellnum++){
                cell = row.createCell(cellnum);
                data = ParamsReflect.getFieldValue(t, excel.getColumnArray()[cellnum]);
                setCellTypeAndValue(cell, data);
            }
        }

        FileOutputStream out = new FileOutputStream(outfile);
        work.write(out);
        out.close();
        // dispose of temporary files backing this workbook on disk
        work.dispose();
        return result;
	}
	
	
	/**
	 * Description:根据不同数据类型，格式化不同单元格
	 * @param cell
	 * @param data TwoTuple<String, Object>
	 * @author around
	 * @date 2017年8月29日下午7:52:33
	 */
	private static void setCellTypeAndValue(Cell cell, TwoTuple<String, Object> data) {
		switch (data.a) {
		case "byte":
		case "short":
		case "int":
		case "long":
			cell.setCellType(CellType.NUMERIC);
			cell.setCellStyle(numberStyle);
			cell.setCellValue(new XSSFRichTextString(String.valueOf(data.b)));
			break;
		case "double":
		case "bigdecimal":
			cell.setCellType(CellType.NUMERIC);
			cell.setCellStyle(decimalStyle);
			cell.setCellValue(((BigDecimal)data.b).doubleValue());
			break;
		case "date":
			cell.setCellType(CellType.STRING);
			cell.setCellStyle(dateStyle);
			cell.setCellValue((Date)data.b);
			break;
		case "boolean":
			cell.setCellType(CellType.BOOLEAN);
			cell.setCellStyle(stringStyle);
			cell.setCellValue(Double.valueOf(String.valueOf(data.b)));
			break;
		case "string":
			cell.setCellType(CellType.STRING);
			cell.setCellStyle(stringStyle);
			cell.setCellValue(String.valueOf(data.b));
			break;
		default:
			cell.setCellType(CellType.STRING);
			cell.setCellStyle(stringStyle);
			cell.setCellValue(String.valueOf(data.b));
			break;
		}
	}
	
	private static Font setFont(Font font, short height, short color, 
			String fontName, boolean bold) {
		font.setFontHeightInPoints(height);
		font.setColor(color);
		font.setFontName(fontName);
		font.setBold(bold);
		//font.setStrikeout(true);
		return font;
	}
	
	private static CellStyle setCellStyle(CellStyle style, short color, 
			HorizontalAlignment horizontal, VerticalAlignment vertical) {
		style.setFillForegroundColor(color);//IndexedColors.GOLD.index
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(BorderStyle.THIN);  
		style.setBorderLeft(BorderStyle.THIN);  
		style.setBorderRight(BorderStyle.THIN);  
		style.setBorderTop(BorderStyle.THIN);  
		style.setAlignment(horizontal);//HorizontalAlignment.CENTER
		style.setVerticalAlignment(vertical);//VerticalAlignment.CENTER
		return style;
	}

	
	public static void main(String[] args) throws IOException {
		//exportExcel(null, "aaa", "E:/All Workspace/mars_workspace/cloud-manager/manager-web/src/main/webapp/", null, null, null);
		//export();
		ExcelBean excel = new ExcelBean();
		String[] titles = new String[]{"a","b","c","啊"};
		excel.setTitleArray(titles);
		excel.setExcelName("test111");
		
		export("E:/fengguo/project/cloud", excel, null);
	}


}
